package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.OpinfoDao;

import net.sf.json.JSONObject;

@Repository
public class OpinfoDaoImpl extends P2PJdbcDao implements OpinfoDao {
	Logger logger = Logger.getLogger(OpinfoDaoImpl.class);

	@Override
	public List queryOpinfoList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("START_DATE");
		String endDate = params.optString("END_DATE");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String username = params.optString("USER_NAME");
		String deviceType = params.optString("DEVICE_TYPE");
		String deviceCityCode = params.optString("DEVICE_CITY_CODE");
		String deviceOperType = params.optString("DEVICE_OPER_TYPE");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("USER_NAME", username);
		paramMap.put("DEVICE_TYPE", deviceType);
		paramMap.put("DEVICE_CITY_CODE", deviceCityCode);
		paramMap.put("DEVICE_OPER_TYPE", deviceOperType);
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append(" SELECT ID,USER_NAME,DEVICE_ID,DEVICE_TYPE, ");
		sql.append("   DEVICE_MODULE_CODE,DEVICE_CITY_CODE,DEVICE_OPER_TYPE, ");
		sql.append("   date_format(DEVICE_OPER_DATE,'%Y-%m-%d %H:%i:%s') as DEVICE_OPER_DATE ");
		sql.append(" FROM t_zhfw_user_token ");
		sql.append(" where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND DEVICE_OPER_DATE >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND DEVICE_OPER_DATE <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" AND USER_NAME like " + "'%" + username + "%' ");
		}
		if (StringUtils.isNotEmpty(deviceCityCode)) {
			sql.append(" AND DEVICE_CITY_CODE like " + "'%" + deviceCityCode + "%' ");
		}
		if ((StringUtils.isNotEmpty(deviceType)) && (deviceType.equals("android"))) {
			sql.append(" AND DEVICE_TYPE ='0' ");
		}
		if ((StringUtils.isNotEmpty(deviceType)) && (deviceType.equals("ios"))) {
			sql.append(" AND DEVICE_TYPE ='1' ");
		}
		if ((StringUtils.isNotEmpty(deviceType)) && (deviceType.equals("状态全部"))) {
			System.out.println("查询全部记录。");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals(""))) {
			System.out.println("查询全部记录。");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("01"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '01' ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("02"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '02'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("03"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '03'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("04"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '04'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("05"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '05'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("06"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '06'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("07"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '07'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("08"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '08'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("09"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '09'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("10"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '10'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("11"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '11'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("12"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '12'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("13"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '13'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("14"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '14'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("15"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '15'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("16"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '16'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("17"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '17'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("18"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '18'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("19"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '19'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("20"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '20'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("21"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '21'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("22"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '22'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("23"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '23'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("24"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '24'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("25"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '25'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("26"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '26'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("27"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '27'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("28"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '28'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("29"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '29'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("30"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '30'  ");
		}
		sql.append(" ORDER BY DEVICE_OPER_DATE DESC LIMIT :start,:limit");
		logger.info("queryOpinfoList sql is " + sql.toString());
		logger.info("queryOpinfoList paramMap is " + paramMap);
		// System.out.println(this.getNamedParameterJdbcTemplate().queryForList(sql.toString(),
		// paramMap).size());
		// return
		// this.getNamedParameterJdbcTemplate().queryForList(sql.toString(),
		// paramMap);

		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new ResultSetExtractor<List<Map<String, Object>>>() {

					public List<Map<String, Object>> extractData(ResultSet rs)
							throws SQLException, DataAccessException {
						List<Map<String, Object>> list = new ArrayList<>();
						while (rs.next()) {
							Map<String, Object> map = new HashMap<>();
							map.put("ID", rs.getString("ID"));
							map.put("USER_NAME", rs.getString("USER_NAME"));
							map.put("DEVICE_ID", rs.getString("DEVICE_ID"));
							map.put("DEVICE_TYPE", rs.getString("DEVICE_TYPE"));
							map.put("DEVICE_MODULE_CODE", rs.getString("DEVICE_MODULE_CODE"));
							map.put("DEVICE_CITY_CODE", rs.getString("DEVICE_CITY_CODE"));
							map.put("DEVICE_OPER_TYPE", rs.getString("DEVICE_OPER_TYPE"));
							map.put("DEVICE_OPER_DATE", rs.getString("DEVICE_OPER_DATE"));

							String deviceOperType = rs.getString("DEVICE_OPER_TYPE");
							String deviceOperTypeValue = "";
							if ("00".equals(deviceOperType)) {
								deviceOperTypeValue = "";
							} else if ("01".equals(deviceOperType)) {
								deviceOperTypeValue = "登录";

							} else if ("02".equals(deviceOperType)) {
								deviceOperTypeValue = "首页刷新";

							} else if ("03".equals(deviceOperType)) {
								deviceOperTypeValue = "购买债权";

							} else if ("04".equals(deviceOperType)) {
								deviceOperTypeValue = "获取债权转让信息";

							} else if ("05".equals(deviceOperType)) {
								deviceOperTypeValue = "获取债权转让详细信息";

							} else if ("06".equals(deviceOperType)) {
								deviceOperTypeValue = "申请借款";

							} else if ("07".equals(deviceOperType)) {
								deviceOperTypeValue = "修改用户信息";

							} else if ("08".equals(deviceOperType)) {
								deviceOperTypeValue = "我的债权";

							} else if ("09".equals(deviceOperType)) {
								deviceOperTypeValue = "vip缴费";

							} else if ("10".equals(deviceOperType)) {
								deviceOperTypeValue = "vip中奖记录";

							} else if ("11".equals(deviceOperType)) {
								deviceOperTypeValue = "vip领奖记录";

							} else if ("12".equals(deviceOperType)) {
								deviceOperTypeValue = "获取交易记录";

							} else if ("13".equals(deviceOperType)) {
								deviceOperTypeValue = "获取银行卡信息";

							} else if ("14".equals(deviceOperType)) {
								deviceOperTypeValue = "添加银行卡信息";

							} else if ("15".equals(deviceOperType)) {
								deviceOperTypeValue = "修改登录密码";

							} else if ("16".equals(deviceOperType)) {
								deviceOperTypeValue = "修改交易密码";

							} else if ("17".equals(deviceOperType)) {
								deviceOperTypeValue = "重置交易密码获取验证码";

							} else if ("18".equals(deviceOperType)) {
								deviceOperTypeValue = "重置交易密码验证验证码";

							} else if ("19".equals(deviceOperType)) {
								deviceOperTypeValue = "重置交易密码通过验证码";

							} else if ("20".equals(deviceOperType)) {
								deviceOperTypeValue = "签到";

							} else if ("21".equals(deviceOperType)) {
								deviceOperTypeValue = "用户反馈";

							} else if ("22".equals(deviceOperType)) {
								deviceOperTypeValue = "提现第一步验证金额，交易密码";

							} else if ("23".equals(deviceOperType)) {
								deviceOperTypeValue = "提现第二步获取验证码";

							} else if ("24".equals(deviceOperType)) {
								deviceOperTypeValue = "提现第三步验证验证码";

							} else if ("25".equals(deviceOperType)) {
								deviceOperTypeValue = "获得我的红包";

							} else if ("26".equals(deviceOperType)) {
								deviceOperTypeValue = "验证pos单号";

							} else if ("27".equals(deviceOperType)) {
								deviceOperTypeValue = "单号投资购买债权";

							} else if ("28".equals(deviceOperType)) {
								deviceOperTypeValue = "资产变动明细";

							} else if ("29".equals(deviceOperType)) {
								deviceOperTypeValue = "获取投标记录";

							} else if ("30".equals(deviceOperType)) {
								deviceOperTypeValue = "刷新账户信息";

							} else if ("31".equals(deviceOperType)) {
								deviceOperTypeValue = "保存实名认证信息";

							} else if ("32".equals(deviceOperType)) {
								deviceOperTypeValue = "获取红点宝列表信息";

							} else if ("33".equals(deviceOperType)) {
								deviceOperTypeValue = "获取红点宝投标记录";

							} else if ("34".equals(deviceOperType)) {
								deviceOperTypeValue = "我的红点宝-个人投资记录";

							}
							map.put("DEVICE_OPER_TYPE_VALUE", deviceOperTypeValue);
							list.add(map);
						}
						return list;
					}
				});

	}

	@SuppressWarnings("deprecation")
	@Override
	public int getOpinfoCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("START_DATE");
		String endDate = params.optString("END_DATE");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String username = params.optString("USER_NAME");
		String deviceType = params.optString("DEVICE_TYPE");
		String deviceCityCode = params.optString("DEVICE_CITY_CODE");
		String deviceOperType = params.optString("DEVICE_OPER_TYPE");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("USER_NAME", username);
		paramMap.put("DEVICE_TYPE", deviceType);
		paramMap.put("DEVICE_CITY_CODE", deviceCityCode);
		paramMap.put("DEVICE_OPER_TYPE", deviceOperType);
		paramMap.put("start", start);
		paramMap.put("limit", limit);

		sql.append(" SELECT COUNT(*) FROM t_zhfw_user_token ");
		sql.append(" where 1=1 ");

		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND DEVICE_OPER_DATE >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND DEVICE_OPER_DATE <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" AND USER_NAME like " + "'%" + username + "%' ");
		}
		if (StringUtils.isNotEmpty(deviceCityCode)) {
			sql.append(" AND DEVICE_CITY_CODE like " + "'%" + deviceCityCode + "%' ");
		}
		if ((StringUtils.isNotEmpty(deviceType)) && (deviceType.equals("android"))) {
			sql.append(" AND DEVICE_TYPE ='0' ");
		}
		if ((StringUtils.isNotEmpty(deviceType)) && (deviceType.equals("ios"))) {
			sql.append(" AND DEVICE_TYPE ='1' ");
		}
		if ((StringUtils.isNotEmpty(deviceType)) && (deviceType.equals("状态全部"))) {
			System.out.println("查询全部记录。");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals(""))) {
			System.out.println("查询全部记录。");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("01"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '01' ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("02"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '02'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("03"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '03'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("04"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '04'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("05"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '05'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("06"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '06'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("07"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '07'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("08"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '08'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("09"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '09'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("10"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '10'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("11"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '11'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("12"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '12'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("13"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '13'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("14"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '14'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("15"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '15'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("16"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '16'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("17"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '17'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("18"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '18'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("19"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '19'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("20"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '20'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("21"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '21'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("22"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '22'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("23"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '23'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("24"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '24'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("25"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '25'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("26"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '26'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("27"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '27'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("28"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '28'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("29"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '29'  ");
		}
		if ((StringUtils.isNotEmpty(deviceOperType)) && (deviceOperType.equals("30"))) {
			sql.append(" AND DEVICE_OPER_TYPE = '30'  ");
		}
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryAppLoadList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm = params.optString("yhm");
		String sblx = params.optString("sblx");
		String dlcs = params.optString("dlcs");
		String oadq = params.optString("oadq");
		String tjr = params.optString("tjr");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select temp4.*,tt4.GYH OA_GYH,tt4.DQ,tt4.BM,tt4.SS,tt4.ZW,tt4.ZJ,tt4.XM OA_XM,tt4.SJHM OA_SJHM");
		sql.append(" from (select temp3.* ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(
				" case when temp3.tjr2 is null or temp3.tjr2='' then case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else temp3.tjr3 end else temp3.tjr2 end else temp3.tjr1 end TJR");
		sql.append(
				" ,case when temp3.tjr1 is null or temp3.tjr1='' then case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(
				" case when temp3.tjr3 is null or temp3.tjr3='' then null else '03' end else '02' end else '01' end KHDJ");
		sql.append(" from (select temp2.* ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(
				" (select tu3.tjr from t_zhfw_user_tgcode tg3 inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE inner join t_zhfw_user tu3");
		sql.append(" on tg1.USER_NAME=tu3.USER_NAME where tg3.USER_NAME=temp2.USER_NAME) end tjr3");
		sql.append(" from (SELECT TEMP.*,tt1.tjr tjr1,case when tt1.tjr is null or tt1.tjr = '' then");
		sql.append(
				" (select tu2.tjr from t_zhfw_user_tgcode tg2 inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(
				" inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME where tg2.USER_NAME=tt1.USER_NAME) end tjr2");
		sql.append(
				" from (select ID,USER_NAME,DEVICE_TYPE,DEVICE_MODULE_CODE,DEVICE_CITY_CODE,date_format(min(DEVICE_OPER_DATE),'%Y-%m-%d %H:%i:%s') MINOPEN ,date_format(max(DEVICE_OPER_DATE),'%Y-%m-%d %H:%i:%s') MAXOPENFROM");
		sql.append(" from d_hd_p2p.t_zhfw_user_token where DEVICE_OPER_TYPE='01' group by USER_NAME) TEMP");
		sql.append(" LEFT JOIN t_zhfw_user tt1 on TEMP.USER_NAME = tt1.USER_NAME and tt1.DISABLED=1");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND TEMP.MINOPEN >= :startDate ");
			paramMap.put("startDate", startDate + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND TEMP.MINOPEN <= :endDate ");
			paramMap.put("endDate", endDate + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" AND TEMP.USER_NAME like :yhm ");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(sblx)) {
			sql.append(" AND TEMP.DEVICE_TYPE =:sblx");
			paramMap.put("sblx", sblx);
		}
		if (StringUtils.isNotEmpty(dlcs)) {
			sql.append(" AND TEMP.DEVICE_CITY_CODE like :dlcs ");
			paramMap.put("dlcs", "%" + dlcs + "%");
		}
		sql.append(") temp2) temp3) temp4");
		sql.append(" left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR where 1=1");
		if (StringUtils.isNotEmpty(oadq)) {
			sql.append(" AND TT4.DQ =:oadq ");
			paramMap.put("oadq",  oadq.trim());
		}
		if (StringUtils.isNotEmpty(tjr)) {
			String[] tjrs=StringUtils.split(tjr, ",");
			String[] sqls=new String[tjrs.length];
			for (int i=0;i<tjrs.length;i++) {
				sqls[i]="temp4.TJR like :tjr"+i;
				paramMap.put("tjr"+i,  tjrs[i]+"%");
			}
			
			sql.append(" AND ("+StringUtils.join(sqls, " OR ")+")");
		}
		sql.append(" order by temp4.MINOPEN desc");
		if (start>=0&&limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryAppLoadList sql is " + sql.toString());
		logger.info("queryAppLoadList paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public int getAppLoadCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm = params.optString("yhm");
		String sblx = params.optString("sblx");
		String dlcs = params.optString("dlcs");
		String oadq = params.optString("oadq");
		String tjr = params.optString("tjr");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from (select temp3.* ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(
				" case when temp3.tjr2 is null or temp3.tjr2='' then case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else temp3.tjr3 end else temp3.tjr2 end else temp3.tjr1 end TJR");
		sql.append(
				" ,case when temp3.tjr1 is null or temp3.tjr1='' then case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(
				" case when temp3.tjr3 is null or temp3.tjr3='' then null else '03' end else '02' end else '01' end KHDJ");
		sql.append(" from (select temp2.* ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(
				" (select tu3.tjr from t_zhfw_user_tgcode tg3 inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE inner join t_zhfw_user tu3");
		sql.append(" on tg1.USER_NAME=tu3.USER_NAME where tg3.USER_NAME=temp2.USER_NAME) end tjr3");
		sql.append(" from (SELECT TEMP.*,tt1.tjr tjr1,case when tt1.tjr is null or tt1.tjr = '' then");
		sql.append(
				" (select tu2.tjr from t_zhfw_user_tgcode tg2 inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(
				" inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME where tg2.USER_NAME=tt1.USER_NAME) end tjr2");
		sql.append(
				" from (select ID,USER_NAME,DEVICE_TYPE,DEVICE_MODULE_CODE,DEVICE_CITY_CODE,date_format(min(DEVICE_OPER_DATE),'%Y-%m-%d %H:%i:%s') MINOPEN ,date_format(max(DEVICE_OPER_DATE),'%Y-%m-%d %H:%i:%s') MAXOPENFROM");
		sql.append(" from d_hd_p2p.t_zhfw_user_token where DEVICE_OPER_TYPE='01' group by USER_NAME) TEMP");
		sql.append(" LEFT JOIN t_zhfw_user tt1 on TEMP.USER_NAME = tt1.USER_NAME and tt1.DISABLED=1");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND TEMP.MINOPEN >= :startDate ");
			paramMap.put("startDate", startDate + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND TEMP.MINOPEN <= :endDate ");
			paramMap.put("endDate", endDate + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" AND TEMP.USER_NAME like :yhm ");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(sblx)) {
			sql.append(" AND TEMP.DEVICE_TYPE =:sblx");
			paramMap.put("sblx", sblx);
		}
		if (StringUtils.isNotEmpty(dlcs)) {
			sql.append(" AND TEMP.DEVICE_CITY_CODE like :dlcs ");
			paramMap.put("dlcs", "%" + dlcs + "%");
		}
		sql.append(") temp2) temp3) temp4");
		sql.append(" left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR where 1=1");
		if (StringUtils.isNotEmpty(oadq)) {
			sql.append(" AND TT4.DQ =:oadq ");
			paramMap.put("oadq",  oadq.trim());
		}
		if (StringUtils.isNotEmpty(tjr)) {
			String[] tjrs=StringUtils.split(tjr, ",");
			String[] sqls=new String[tjrs.length];
			for (int i=0;i<tjrs.length;i++) {
				sqls[i]="temp4.TJR like :tjr"+i;
				paramMap.put("tjr"+i,  tjrs[i]+"%");
			}
			sql.append(" AND ("+StringUtils.join(sqls, " OR ")+")");
		}
		logger.info("getAppLoadCount sql is " + sql.toString());
		logger.info("getAppLoadCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> getAppLoginTj(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String roleParam = params.optString("roleParam");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select temp4.ID,tt4.DQ,count(*) DLSL");
		sql.append(" from (select temp3.ID,temp3.MINOPEN,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(
				" case when temp3.tjr2 is null or temp3.tjr2='' then case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else temp3.tjr3 end else temp3.tjr2 end else temp3.tjr1 end TJR");
		sql.append(" from (select temp2.* ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(
				" (select tu3.tjr from t_zhfw_user_tgcode tg3 inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE inner join t_zhfw_user tu3");
		sql.append(" on tg1.USER_NAME=tu3.USER_NAME where tg3.USER_NAME=temp2.USER_NAME) end tjr3");
		sql.append(" from (SELECT TEMP.*,tt1.tjr tjr1,case when tt1.tjr is null or tt1.tjr = '' then");
		sql.append(
				" (select tu2.tjr from t_zhfw_user_tgcode tg2 inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(
				" inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME where tg2.USER_NAME=tt1.USER_NAME) end tjr2");
		sql.append(
				" from (select ID,USER_NAME,date_format(min(DEVICE_OPER_DATE),'%Y-%m-%d %H:%i:%s') MINOPEN");
		sql.append(" from d_hd_p2p.t_zhfw_user_token where DEVICE_OPER_TYPE='01' group by USER_NAME) TEMP");
		sql.append(" LEFT JOIN t_zhfw_user tt1 on TEMP.USER_NAME = tt1.USER_NAME and tt1.DISABLED=1");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND TEMP.MINOPEN >= :startDate ");
			paramMap.put("startDate", startDate + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND TEMP.MINOPEN <= :endDate ");
			paramMap.put("endDate", endDate + " 23:59:59");
		}
		sql.append(") temp2) temp3) temp4");
		sql.append(" left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR where 1=1");
		if (StringUtils.isNotEmpty(roleParam)) {
			String[] tjrs=StringUtils.split(roleParam, ",");
			String[] sqls=new String[tjrs.length];
			for (int i=0;i<tjrs.length;i++) {
				sqls[i]="temp4.TJR like :tjr"+i;
				paramMap.put("tjr"+i,  tjrs[i]+"%");
			}
			sql.append(" AND ("+StringUtils.join(sqls, " OR ")+")");
		}
		sql.append(" group by tt4.DQ order by convert(tt4.DQ using gbk) desc");
		logger.info("getAppLoginTj sql is " + sql.toString());
		logger.info("getAppLoginTj paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}
}
